/*
 *                  BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 * 
 * Created on Nov 1, 2007
 * 
 */

package org.biojava.services.das.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.biojava.services.das.registry.DasRegistrySql;

import dasregistry.CourseRegistrationBean;

public class CourseDAO {


	public boolean registrationOpen(Connection conn) throws SQLException {
		String sql = "select course_open from admin";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();

		boolean open = false;

		while (set.next()){
			int o = set.getInt(1);
			if ( o == 1)
				open = true;
		}
		ps.close();
		set.close();
		//open=true;
		return open;
	}


	public boolean isRegisteredUser(Connection conn, String openid) throws SQLException{
		String sql = "select count(*) from course where openid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,openid);
		boolean found = false;
		ResultSet set = ps.executeQuery();
		while (set.next()){
			int count = set.getInt(1);
			if ( count >0 )
				found = true;
		}
		set.close();
		ps.close();

		return found;

	}

	public CourseRegistrationBean getDataForUser(Connection conn, String openid) throws SQLException{

		CourseRegistrationBean course = new CourseRegistrationBean();

		String sql = "select name, openid, email, day1, day2, day3, suggestedTopic, accomodation1," +
		" accomodation2,accomodation3, accomodation4, specialRequirement, comment, flightarrival, flightarrivalnr," +
		" flightdeparture, flightdeparturenr,funding,dinner,language from course where openid = ? ";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,openid);

		ResultSet set = ps.executeQuery();
		if (set.next()){
			course.setName(set.getString("name"));
			course.setOpenid(set.getString("openid"));
			course.setEmail(set.getString("email"));
			course.setDay1(set.getString("day1"));
			course.setDay2(set.getString("day2"));
			course.setDay3(set.getString("day3"));
			course.setSuggestedTopic(set.getString("suggestedTopic"));
			course.setAccomodation1(set.getString("accomodation1"));
			course.setAccomodation2(set.getString("accomodation2"));
			course.setAccomodation3(set.getString("accomodation3"));
			course.setAccomodation4(set.getString("accomodation4"));
			course.setSpecialRequirement(set.getString("specialrequirement"));
			course.setComment(set.getString("comment"));
			course.setFlightArrival(set.getString("flightarrival"));
			course.setFlightArrivalNr(set.getString("flightarrivalnr"));
			course.setFlightDeparture(set.getString("flightdeparture"));
			course.setFlightDepartureNr(set.getString("flightdeparturenr"));
			course.setFunding(set.getString("funding"));
			course.setDinner(set.getString("dinner"));
			course.setLanguage(set.getString("language"));
			
		}

		set.close();
		ps.close();

		return course;
	}

	public boolean registerOrUpdate(Connection conn, CourseRegistrationBean course, String openid) throws SQLException{

//		if ( ! registrationOpen(conn)) {
//			System.out.println("returning fals to never updating data");
//			return false;
//		}

		if ( isRegisteredUser(conn, openid)) {
			return updateRegistration(conn,course,openid);
		} else {//changed logic here as even though registration is closed I want people to be able to update their data
			if ( ! registrationOpen(conn)) {
				System.out.println("returning false to never updating data");
				//we don't want to allow a new registration so if registration and they are not a registered user return false
				return false;
			}
			
			return newRegistration(conn,course,openid);
		}



	}

	private boolean updateRegistration(Connection conn, CourseRegistrationBean course, String openid) throws SQLException{


		String sql = "update course set name=?, openid=?, email=?, day1=?, day2=?, day3=?, suggestedTopic=?, accomodation1=?," +
		" accomodation2=?,accomodation3=?,accomodation4=?, specialRequirement=?, comment=?, flightarrival=?, flightarrivalnr=?," +
		" flightdeparture=?, flightdeparturenr=? ,funding=? ,dinner=?,language=? where openid=? ";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,course.getName());
		ps.setString(2,course.getOpenid());
		ps.setString(3,course.getEmail());
		ps.setString(4,course.getDay1());
		ps.setString(5,course.getDay2());
		ps.setString(6, course.getDay3());
		ps.setString(7, course.getSuggestedTopic());
		ps.setString(8,course.getAccomodation1());
		ps.setString(9,course.getAccomodation2());
		ps.setString(10,course.getAccomodation3());
		ps.setString(11,course.getAccomodation4());
		ps.setString(12,course.getSpecialRequirement());
		ps.setString(13,course.getComment());
		ps.setString(14,course.getFlightArrival());
		ps.setString(15,course.getFlightArrivalNr());
		ps.setString(16,course.getFlightDeparture());
		ps.setString(17,course.getFlightDepartureNr());
		ps.setString(18,course.getFunding());
		ps.setString(19,course.getDinner());
		ps.setString(20,course.getLanguage());

		ps.setString(21,course.getOpenid());
		ps.executeUpdate();

		ps.close();
		return true;
	}

	private boolean newRegistration(Connection conn, CourseRegistrationBean course, String openid) throws SQLException{

		boolean flag = true;


		String sql = "insert into course (name, openid, email, day1, day2, day3, suggestedTopic, accomodation1," +
		" accomodation2,accomodation3, accomodation4, specialRequirement, comment, flightarrival, flightarrivalnr, " +
		" flightdeparture, flightdeparturenr,funding,dinner,language) " +
		" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,course.getName());
		ps.setString(2,course.getOpenid());
		ps.setString(3,course.getEmail());
		ps.setString(4,course.getDay1());
		ps.setString(5,course.getDay2());
		ps.setString(6,course.getDay3());
		ps.setString(7,course.getSuggestedTopic());
		ps.setString(8,course.getAccomodation1());
		ps.setString(9,course.getAccomodation2());
		ps.setString(10,course.getAccomodation3());
		ps.setString(11,course.getAccomodation4());
		ps.setString(12,course.getSpecialRequirement());
		ps.setString(13,course.getComment());
		ps.setString(14,course.getFlightArrival());
		ps.setString(15,course.getFlightArrivalNr());
		ps.setString(16,course.getFlightDeparture());
		ps.setString(17,course.getFlightDepartureNr());
		ps.setString(18,course.getFunding());
		ps.setString(19,course.getDinner());
		ps.setString(20,course.getLanguage());
		
		ps.executeUpdate();

		ps.close();

		sendNotificationEmail(course);

		return flag;
	}

	private void sendNotificationEmail(CourseRegistrationBean course){

		DasRegistrySql drs = new DasRegistrySql();

		String from = "dasregistry@sanger.ac.uk";
		String[] recipients = new String[2];
		recipients[0] = course.getEmail();
		recipients[1] = from;

		String subject = "confirmation of registration DAS workshop";
		String message = "Dear " + course.getName() +", <br/>\n<br/>\n" +
		"You have been registered for the DAS workshop (March 9th,10th,11th 2009) at the Genome Campus in Hinxton. <br/>\n" ;
	
		message += "We will contact you with more detailed information later on. If you have any questions in the meanwhile," +
		" please don't hesitate to contact <a href=\"jw12@sanger.ac.uk\">jw12@sanger.ac.uk</a>. <br/>\n<br/>\n";
				
		message +="your registration details:" + "<br/>\n" + "<br/>\n<pre>" + course + "<pre><br/>\n";
		
		message += "It is still possible to update your data at <a href=\"http://www.dasregistry.org/course_register.jsp\">http://www.dasregistry.org/course_register.jsp</a> <br/>\n<br/>\n";
		
		message += "This message has been generated automatically.<br/>\n";

		try {
			drs.postMail(recipients, subject, message, from);
		}	catch (Exception e){
			e.printStackTrace();
		}
	}
}
